"""
Copyright 2012 Wieser Dietmar, Margit Mutschlechner.

This file is part of Multimedia Social Networking Platform.

Multimedia Social Networking Platform is free software: 
you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

Multimedia Social Networking Platform is distributed
in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with Multimedia Social Networking Platform.  
If not, see <http://www.gnu.org/licenses/>.

"""

import MySQLdb
import traceback
from msnp.logger import Logger
from msnp.persistence import FileAccessor
from msnp.persistence.mysql.player_accessors import AmarokAccessor
from msnp.model import MediaModel
from msnp.persistence.mysql import DBErrorHandler

DATABASE = ""
USER = ""
PWD = ""
SCHEMA = ""
#PATH_SQL_FILE = "/home/franz/workspace/MSNP_0_4/db_sql/ptp_sql/"
PATH_SQL_FILE = "../db_sql/player_sql/"
#PATH_SQL_FILE = "/home/itie/Uni/master/masterarbeit/MSNP_0_10/db_sql/player_sql/"

TYPE_PROC = "_proc"
TYPE_INSERT_TRIGGER = "_insert_trigger"
TYPE_UPDATE_TRIGGER = "_update_trigger"
FILE_SUFFIX_DROP = "_drop"

__triggerList = [AmarokAccessor.TABLE_ALBUMS, AmarokAccessor.TABLE_ARTISTS, AmarokAccessor.TABLE_GENRES, AmarokAccessor.TABLE_LYRICS, AmarokAccessor.TABLE_STATISTICS, AmarokAccessor.TABLE_TRACKS, AmarokAccessor.TABLE_COMPOSERS]
__typesList = [TYPE_PROC, TYPE_INSERT_TRIGGER, TYPE_UPDATE_TRIGGER]

def __openConnection():
    """Opens a connection to the media database with the global arguments.
    
    Returns:
        Returns a tuple of (Connection, Cursor) or the tuple (None, None) if no connection could be set up.
    """
    
    database = MySQLdb.connect(DATABASE, USER, PWD, charset="utf8", use_unicode=True)
    if database != None:
        cursor = database.cursor()
        return (database, cursor)
    return (None, None)

def __closeConnection(database):
    """Close the connection to the database given as an argument.
    
    Args:
        database: The Connection object representing the database where the connection should be closed.
    """
    if database != None:
        database.close()
    else:
        Logger.info('database is None while closing')
        

def existsSchemaAndTables(database, cursor, schema, player):
    """Check whether there exists a player database with the given schema and the correct tables.
    
    Args:
        database: A Connection object representing the player database. 
        cursor: A Cursor object for the database.
        schema: A String representing the schema name.
        player: A String representing the type of player, eg. AMAROK.
        
    Returns:
        True is returned, if there exists a media database with the given schema and the correct tables, False otherwise.
    """
    if player == MediaModel.Player.AMAROK().getName():
        return AmarokAccessor.existsSchemaAndTables(database, cursor, schema)
    return False


def initDatabaseCredentials(host, username, password, schema):
    """Sets the global variables DATABASE, USER, PWD and SCHEMA to the corresponding given arguments.
    
    Args:
        host: A String representing the host name of the database.
        username: A String representing the user name.
        password: A String representing the password of the user.
        schema: A String representing the schema name.
    """
    #this method is a "setter" for these global variables. python interprets this as local variables without the "global"-statement.
    global DATABASE
    global USER
    global PWD
    global SCHEMA
    DATABASE = host
    USER = username
    PWD = password
    SCHEMA = schema
        
def initTriggers(mediaDBSchema):
    """Initializes the triggers that log insert and update events of the player database into a table of the media database.
    
    Args:
        mediaDBSchema: A String containing the schema name of the media database.
    """
    global __triggerList
    global __typesList
    global FILE_SUFFIX_DROP
    
    try:
        (database, cursor) = __openConnection()
        # create triggers
        for trigger in __triggerList:
            for type in __typesList:
                __dropOrCreateTriggers(database, cursor, trigger, type, FILE_SUFFIX_DROP)
                __dropOrCreateTriggers(database, cursor, trigger, type, "", mediaDBSchema)
        Logger.info("triggers initalized")
    except:
        Logger.info('exception initializing player database triggers (should not happen!!!)')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPlayerDBError(DBErrorHandler.DBErrorType.TRIGGER, traceback.format_exc())
    finally:
        __closeConnection(database)

def __dropOrCreateTriggers(database, cursor, triggerName, type, drop, mediaDBSchema=""):
    """Initializes the triggers that log insert and update events of the player database into a table of the media database.
    
    Args:
        database: A Connection object representing the player database. 
        cursor: A Cursor object for the database.
        triggerName: A String containing the name of the table where the trigger should be installed, eg. artist.
        type: A String containing the type of trigger, either "insert" or "update".
        drop: A String, either "drop" if the trigger should be dropped, or a void String if the trigger should be installed.
        mediaDBSchema: A String containing the name of the schema of the media database.
    """
    global PATH_SQL_FILE
    global TYPE_PROC
    
    query = FileAccessor.readFromFile(PATH_SQL_FILE + triggerName + "" + type + "" + drop + ".sql")
    if drop == "" and type == TYPE_PROC:
        #procedures
        if triggerName == AmarokAccessor.TABLE_ARTISTS or triggerName == AmarokAccessor.TABLE_GENRES or triggerName == AmarokAccessor.TABLE_LYRICS or triggerName == AmarokAccessor.TABLE_COMPOSERS:
            query = query % (SCHEMA, mediaDBSchema,)
        elif triggerName == AmarokAccessor.TABLE_ALBUMS:
            query = query % (SCHEMA, mediaDBSchema, mediaDBSchema,)
        elif triggerName == AmarokAccessor.TABLE_STATISTICS:
            query = query % (SCHEMA, mediaDBSchema, mediaDBSchema, mediaDBSchema, mediaDBSchema,)
        elif triggerName == AmarokAccessor.TABLE_TRACKS:
            query = query % (SCHEMA, mediaDBSchema, mediaDBSchema, mediaDBSchema, mediaDBSchema, mediaDBSchema, mediaDBSchema, mediaDBSchema, mediaDBSchema, mediaDBSchema, mediaDBSchema,)
    elif drop == "":
        #triggers
        query = query % (SCHEMA, SCHEMA, SCHEMA)
    else:
        #drop
        query = query % (SCHEMA)
    AmarokAccessor.executeQuery(database, cursor, query)


############
## SELECT ##
############

def selectTrackIdsForTitleArtistAndCollection(player, title, artistName, collectionName):
    """Selects from the given player database the ids for all tracks that correspond to the given title, artist name and collection name.
    
    Args:
        player: A String representing the player which database should be queried.
        title: A String containing the title of the track.
        artistName: A String containing the track's artist name.
        collectionName: A String containing the track's collection name.
    
    Returns:
        A list of integers representing the ids is returned, or None if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                if player == MediaModel.Player.AMAROK().getName():
                    return AmarokAccessor.selectTrackIdsForTitleArtistAndCollection(database, cursor, SCHEMA, title, artistName, collectionName)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting trackid for title, artist and collection')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPlayerDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectFileUrlForTrackId(player, trackId):
    """Selects from the given player database the file url for the given track id.
    
    Args:
        player: A String representing the player which database should be queried.
        trackId: An integer containing the id of the track.
    
    Returns:
        A String containing the retrieved url is returned, or None if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                if player == MediaModel.Player.AMAROK().getName():
                    return AmarokAccessor.selectFileUrlForTrackId(database, cursor, SCHEMA, trackId)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting fileurl for trackId ' + str(trackId) + ' for player ' + player)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPlayerDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None
        
def selectAudio(player, trackId):
    """Selects from the given player database the audio with the given track id or all audios if trackId is None.
    
    Args:
        player: A String representing the player which database should be queried.
        trackId: An integer containing the id of the track.
    
    Returns:
        A list of Audio objects containing the retrieved audios of the player database, or None if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                if player == MediaModel.Player.AMAROK().getName():
                    audios = AmarokAccessor.selectAudio(database, cursor, SCHEMA, trackId)
                    return audios
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting audios for player ' + player)
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPlayerDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

def selectTrackId(player, tablename, rowId):
    """Selects from the given player database the id of the track that has an entry in the given row in the given table.
    Args:
        player: A String representing the player which database should be queried. Default AMAROK.
        tablename: A String containing the name of a certain table of the player database.
        rowId: An integer value containing a certain row number.
    
    Returns:
        An integer value containing the retrieved track id is returned, or None if an error occurred.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                if player == MediaModel.Player.AMAROK().getName():
                    trackId = AmarokAccessor.selectTrackId(database, cursor, SCHEMA, tablename, rowId)
                    return trackId
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception selecting trackId for player ' + player + ', tablename ' + tablename + ', rowId ' + str(rowId))
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPlayerDBError(DBErrorHandler.DBErrorType.SELECT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None

###################
## INSERT-UPDATE ##
###################

def insertOrUpdateLyricsForUrl(player, lyrics, url):
    """Inserts or updates in the given player database the lyrics of the track that corresponds to the given url.
    
    Args:
        player: A String representing the player which database should be queried. Default AMAROK.
        lyrics: A String containing the lyrics to insert or update.
        url: A String containing the file url of the track which lyrics should be updated or inserted.
    
    Returns:
        True is returned if the update or insertion was successful, False otherwise.
    """
    try:
        (database, cursor) = __openConnection()
        if (database != None):
            if (cursor != None):
                if player == MediaModel.Player.AMAROK().getName():
                    return AmarokAccessor.insertOrUpdateLyricsForUrl(database, cursor, SCHEMA, lyrics, url)
            else:
                Logger.info('cursor is None after opening')
        else:
            Logger.info('database is None after opening')
    except:
        Logger.info('exception inserting or updating lyrics')
        Logger.info(traceback.format_exc())
        DBErrorHandler.processPlayerDBError(DBErrorHandler.DBErrorType.INSERT, traceback.format_exc())
    finally:
        __closeConnection(database)
    return None
